/*
 * 学生管理DAO类
 */

package com.floatpoint.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;

import com.floatpoint.db.DbConnManager;
import com.floatpoint.model.Student;

public class StudentDAO {
	public final static int PAGESIZE = 5; //每页大小
	
	/*
	 * 根据页码，取出记录
	 * pageNo = 1, limit 0,5
	 * pageNo = 2, limit 5,5
	 * pageNo = 3, limit 10,5
	 */
	public ArrayList<Student> getStudentList(int pageNo) {
		ArrayList<Student> list = new ArrayList<Student>();
		
		Connection conn = DbConnManager.getConnection();
		
		int index = (pageNo - 1)*PAGESIZE;
		String sql = "select * from student limit " + index  + "," + PAGESIZE;
		System.out.println(sql);
		PreparedStatement pstm = null;
		try {
			pstm = conn.prepareStatement(sql);
			
			ResultSet rs = pstm.executeQuery();
			
			while (rs.next()) {
				int id = rs.getInt("id");
				String xh = rs.getString("xh");
				String name = rs.getString("name");
				int sex = rs.getInt("sex");
				
				Student student = new Student();
				student.setId(id);
				student.setXh(xh);
				student.setName(name);
				student.setSex(sex);
				
				list.add(student);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		if (pstm != null) {
			try {
				pstm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return list;
	}
	
	
	public int getRecordCount() {
		int ret = 0;
		
		Connection conn = DbConnManager.getConnection();
		
		String sql = "select count(*) as cnt from student";
		System.out.println(sql);
		PreparedStatement pstm = null;
		try {
			pstm = conn.prepareStatement(sql);
			ResultSet rs = pstm.executeQuery();
			
			if (rs.next()) {
				ret = rs.getInt("cnt");
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		if (pstm != null) {
			try {
				pstm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return ret;
	}
	
	

	public static void main(String[] args) {
		StudentDAO sDAO = new StudentDAO();
		
		ArrayList<Student> list = sDAO.getStudentList(2);
		
//		for (int i = 0; i < list.size(); i++) {
//			Student student = list.get(i);
//			
//		}
			
		for (Iterator<Student> iterator = list.iterator(); iterator.hasNext();) {
			Student student = (Student) iterator.next();
			
			System.out.println(student.getId() + "," + student.getXh() + "," 
					+ student.getName() + "," +  student.getSex());
		}
		
	}
}
